package com.bwf.jdbc;

import java.sql.*;
import java.util.Date;

/**
 * @Version v1.0
 * @Description: TODO
 * @Author ZHANZHONGXIN
 * @Date 2022/6/1 9:46
 * @Copyright 博为峰
 */
public class JdbcDemo02 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
//        query();
//        update();
        Date date = new Date();
        Student student = new Student("孙越", "男", date, 1999, date, date, 2);
        insert(student);
    }

    private static void update() {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", "123456");
             Statement statement = connection.createStatement();) {
            int i = statement.executeUpdate("update t_student set salary = 20000 where id = 2");
        } catch (Exception e) {

        }
    }

    private static void insert(Student student) {
        //
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", "123456");
            //SQL 语句被预编译并存储在 PreparedStatement 对象中
            String sql = "insert into `t_student`(name,sex,salary,birthday,create_time,update_time,class_id) values (?, ?, ?, ?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);
            //设置数据
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setInt(3, student.getSalary());
            //java.util.Date
            Date birthday = student.getBirthday();
            //setDate需要java.sql.Date
            preparedStatement.setDate(4, new java.sql.Date(birthday.getTime()));
            Date createTime = student.getCreateTime();
            preparedStatement.setDate(5, new java.sql.Date(createTime.getTime()));
            preparedStatement.setDate(6, new java.sql.Date(student.getUpdateTime().getTime()));
            preparedStatement.setInt(7, student.getClassId());
            //执行
            int i = preparedStatement.executeUpdate();
            System.out.println("i = " + i);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    private static void query() throws SQLException {
        //1.加载驱动
//        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", "123456");
        //3.执行SQL
        Statement statement = connection.createStatement();
        //4.结果集
        ResultSet resultSet = statement.executeQuery("select * from t_student");

        while (resultSet.next()) {
            String name = resultSet.getString("name");
            System.out.println(name);
            int salary = resultSet.getInt(5);
            System.out.println("salary = " + salary);
        }
    }
}
